package com.hualude.roadsign.repository;

import com.hualude.roadsign.dto.HighLeverFaultDto;
import com.hualude.roadsign.dto.DTHighLeverFaultDto;
import com.hualude.roadsign.dto.DTRequestDto;
import org.hibernate.query.NativeQuery;
import org.hibernate.transform.Transformers;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.List;

/**
 * Created by xu_jiaHui on 2018/3/24.
 */
public class HighLeverFaultRepositoryImpl {
    @PersistenceContext
    private EntityManager em;

    public Integer getHighLeverReportFaultCount4DT(Integer actorId, Integer userId, Integer orgId, String strSearch) throws Exception {
        String sql = "select count(*) " +
                "FROM (((tb_highLever_fault cf JOIN tb_user u ON u.UserId=cf.HighLeverFaultCreatorId)\n" +
                "JOIN tb_org o ON o.OrgId=cf.HighLeverOrgId)JOIN tb_highLever c ON c.HighLeverId=cf.HighLeverId)\n" +
                "WHERE concat(c.HighLeverStartRoad,c.HighLeverEndRoad,c.HighLeverRoadName)like '%" + strSearch + "%'";
        if (actorId == 3) {
            sql += " and cf.HighLeverOrgId=" + orgId + "";
        } else if (actorId == 6) {
            sql += " and cf.HighLeverFaultCreatorId=" + userId + "";
        }
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public List<DTHighLeverFaultDto> getHighLeverReportFault4DT(DTRequestDto dtRequestDto, Integer actorId, Integer userId, Integer orgId) throws Exception {
        String sql = "SELECT cf.*,u.UserName AS highLeverFaultCreatorName,u2.UserName AS highLeverMaintainerName,u3.UserName " +
                "AS highLeverMainCheckerName, o.OrgName AS highLeverOrgName,c.HighLeverPhoto from tb_highLever_fault cf left join tb_user u on" +
                " u.UserId=cf.HighLeverFaultCreatorId left join tb_user u2 on u2.userId = cf.HighLeverMaintainerId " +
                "left join tb_user u3 on u3.userId=cf.HighLeverMainCheckerId left join tb_org o on o.orgId=cf.HighLeverOrgId " +
                "left join tb_highLever c on c.HighLeverId=cf.HighLeverId " +
                "WHERE concat(c.HighLeverRoadName,c.HighLeverStartRoad,c.HighLeverEndRoad)like '%" + dtRequestDto.getSearch() + "%' ";
        if (actorId == 3) {
            sql += " and cf.HighLeverOrgId=" + orgId + "";
        } else if (actorId == 6) {
            sql += " and cf.HighLeverFaultCreatorId=" + userId + "";
        }
        sql += " AND cf.HighLeverCheck IN (0,1,-1,2) \n" +
                " order by find_in_set( cf.HighLeverCheck ,'0,2,1,-1') , cf.HighLeverFaultTime DESC";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(DTHighLeverFaultDto.class));
        List<DTHighLeverFaultDto> highLeverFaultDtos = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return highLeverFaultDtos;
    }

    public List<DTHighLeverFaultDto> getHighLeverFault4DT(DTRequestDto dtRequestDto, Integer actorId, Integer orgId) throws Exception {
        String sql = "SELECT mf.HighLeverFaultId,mf.HighLeverId,HighLeverFaultPhoto,mf.HighLeverFaultCreatorId,HighLeverFaultTime," +
                "HighLeverCheckerId,HighLeverMoney,HighLeverFaultLevel,HighLeverFaultType,mf.HighLeverFaultReason,\n" +
                "HighLeverMainCheckerId,HighLeverCheck,HighLeverMainCheck,mf.HighLeverOrgId,mf.HighLeverFeedback,mf.HighLeverMainOrgId,OrgName AS highLeverOrgName,HighLeverPhoto \n" +
                "FROM tb_highLever_fault mf,tb_highLever sm,tb_org o\n" +
                "WHERE mf.HighLeverOrgId=o.OrgId AND mf.HighLeverId = sm.HighLeverId " +
                " AND o.orgId like '%" + dtRequestDto.getSearch() + "%' AND mf.HighLeverCheck=1 ";
//        根据主管审核人和系统管理员区分
        if (actorId == 7) {
            sql += " AND mf.HighLeverMainOrgId=" + orgId + "";
        }
        sql += "  AND  mf.HighLeverMainCheck IN (0,1,-1) \n" +
                " order by  find_in_set( mf.HighLeverMainCheck ,'0,1,-1') , mf.HighLeverFaultTime DESC";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(DTHighLeverFaultDto.class));
        List<DTHighLeverFaultDto> machineFaultDtos = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return machineFaultDtos;
    }

    public Integer getHighLeverFaultCount4DT(DTRequestDto dtRequestDto, Integer actorId, Integer orgId) throws Exception {
        String sql = "SELECT count(*)\n" +
                "FROM tb_highLever_fault mf,tb_highLever sm,tb_org o\n" +
                "WHERE mf.HighLeverOrgId=o.OrgId AND mf.HighLeverId = sm.HighLeverId " +
                " AND o.orgId like '%" + dtRequestDto.getSearch() + "%' AND mf.HighLeverMainCheck=1 ";
//        根据主管审核人和系统管理员区分
        if (actorId == 7) {
            sql += " AND mf.HighLeverMainOrgId=" + orgId + "";
        }
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public List<DTHighLeverFaultDto> getHighLeverFaultHandle4DT(DTRequestDto dtRequestDto, String actorIds, Integer orgId, Integer machineMaintenanceStatus) throws Exception {
        String sql = "SELECT u.userName AS highLeverFaultCreatorName, HighLeverFaultId,OrgName AS highLeverOrgName,HighLeverFaultCreatorId,HighLeverFaultTime,HighLeverCheckerId,HighLeverFaultType,HighLeverFaultLevel,HighLeverMaintenancePhoto,HighLeverMaintainerId," +
                "HighLeverFaultReason,HighLeverMaintenanceStatus,HighLeverMainCheckTime,HighLeverMaintenanceExplain,HighLeverLongitude,HighLeverLatitude," +
                "HighLeverMainCheckerId,mf.HighLeverId,HighLeverMaintenanceContent,HighLeverFeedback,HighLeverStartRoad,HighLeverEndRoad,HighLeverRoadName," +
                "HighLeverFaultPhoto,HighLeverPhoto,HighLeverArea,HighLeverCity,HighLeverProvince,mf.HighLeverOrgId,mf.highLeverMoney,mf.highLeverMaintenanceTime\n" +
                "FROM tb_highLever sm,tb_highLever_fault mf,tb_org o,tb_user u\n" +
                "WHERE mf.HighLeverId=sm.HighLeverId AND mf.HighLeverOrgId=o.OrgId AND u.userId=mf.highLeverFaultCreatorId AND mf.HighLeverMainCheck in (1,2) " +
                "AND concat(sm.HighLeverStartRoad, sm.HighLeverEndRoad,sm.HighLeverRoadName) like '%" + dtRequestDto.getSearch() + "%' AND mf.HighLeverCheck in (1,2) ";
        //根据维修人员和系统管理员区分
        if (machineMaintenanceStatus != 3) {
            sql += " and mf.HighLeverMaintenanceStatus = " + machineMaintenanceStatus + " ";
        }

        if (actorIds.indexOf('1') >= 0) {
            sql += "";
        } else if (actorIds.indexOf('3') >= 0 || actorIds.indexOf('5') >= 0) {
            sql += " AND mf.HighLeverOrgId =" + orgId + "";
        } else if (actorIds.indexOf('7') >= 0) {
            sql += " AND mf.HighLeverMainOrgId =" + orgId + "";
        }
        sql += " order by mf.HighLeverMaintenanceStatus ASC ";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(DTHighLeverFaultDto.class));
        List<DTHighLeverFaultDto> solarFaultDtos = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return solarFaultDtos;
    }

    public Integer getHighLeverFaultHandleCount4DT(DTRequestDto dtRequestDto, String actorIds, Integer orgId, Integer machineMaintenanceStatus) throws Exception {
        String sql = "SELECT count(*)\n" +
                "FROM tb_signal_machine sm,tb_machine_fault mf,tb_org o\n" +
                "WHERE mf.SignalMachineId=sm.SignalMachineId AND mf.MachineOrgId=o.OrgId AND mf.MachineMainCheck=1 " +
                "AND concat(sm.SignalMachineStartRoad, sm.SignalMachineEndRoad) like '%" + dtRequestDto.getSearch() + "%' AND mf.MachineCheck=1 ";
        //根据维修人员和系统管理员区分
        if (machineMaintenanceStatus != 3) {
            sql += " and mf.machineMaintenanceStatus = " + machineMaintenanceStatus + " ";
        }
        if (actorIds.indexOf('1') >= 0) {
            sql += "";
        } else if (actorIds.indexOf('3') >= 0 || actorIds.indexOf('5') >= 0) {
            sql += " AND mf.MachineOrgId =" + orgId + "";
        } else if (actorIds.indexOf('7') >= 0) {
            sql += " AND mf.MachineMainOrgId =" + orgId + "";
        }
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public List<HighLeverFaultDto> getHighLeverFaultCheckList4App(Integer orgId, String actorIds, Integer faultStatus,
                                                                  Integer count, Integer size) throws Exception {
        String sql = " SELECT mf.HighLeverFaultId,mf.HighLeverId,mf.HighLeverFaultPhoto,mf.HighLeverFaultType,mf.HighLeverFaultLevel," +
                "mf.HighLeverFaultReason,mf.HighLeverFaultTime,mf.HighLeverCheck,mf.HighLeverMainCheck,mf.HighLeverMainCheckTime " +
                "FROM tb_highLever_fault mf ";
        if (actorIds.indexOf("1") >= 0) {//系统管理员
            if (faultStatus == 0) {//待审核
                sql += " WHERE (mf.HighLeverCheck =0) OR (mf.HighLeverMainCheck =0 AND mf.HighLeverCheck=1 )  ";
            } else if (faultStatus == 1) {//已通过
                sql += " WHERE  mf.HighLeverMainCheck =1 AND mf.HighLeverCheck=1  ";
            } else if (faultStatus == 2) {//未通过
                sql += " WHERE ( mf.HighLeverCheck =-1) OR (mf.HighLeverMainCheck =-1 AND mf.HighLeverCheck=1 ) ";
            } else {
                return null;
            }
        } else if (actorIds.indexOf("3") >= 0) {//审核员
            if (faultStatus == 0) {//待审核
                sql += " WHERE mf.HighLeverCheck=0 AND mf.HighLeverOrgId=" + orgId + " ";
            } else if (faultStatus == 1) {//已通过
                sql += " WHERE mf.HighLeverCheck=1 AND mf.HighLeverOrgId=" + orgId + " ";
            } else if (faultStatus == 2) {//未通过
                sql += " WHERE mf.HighLeverCheck=-1 AND mf.HighLeverOrgId=" + orgId + " ";
            } else {
                return null;
            }
        } else if (actorIds.indexOf("7") >= 0) {//主管审核员
            if (faultStatus == 0) {//待审核
                sql += " WHERE mf.HighLeverCheck=1 AND mf.HighLeverMainCheck=0 AND mf.HighLeverMainOrgId=" + orgId + " ";
            } else if (faultStatus == 1) {//已通过
                sql += " WHERE mf.HighLeverCheck=1 AND mf.HighLeverMainCheck=1 AND mf.HighLeverMainOrgId=" + orgId + " ";
            } else if (faultStatus == 2) {//未通过
                sql += " WHERE mf.HighLeverCheck=1 AND mf.HighLeverMainCheck=-1 AND mf.HighLeverMainOrgId=" + orgId + " ";
            } else {
                return null;
            }
        }
        sql += " ORDER BY mf.HighLeverFaultTime DESC ";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(HighLeverFaultDto.class));
        return query.setFirstResult((count - 1) * size).setMaxResults(size).getResultList();
    }


    public List<HighLeverFaultDto> getHighLeverFaultHandleList4App(Integer orgId, Integer maintenanceStatus, String actorIds, Integer count, Integer size) throws Exception {
        String sql = "SELECT mf.HighLeverFaultId,mf.HighLeverId,mf.HighLeverFaultPhoto,mf.HighLeverFaultType,mf.HighLeverFaultLevel," +
                "mf.HighLeverFaultReason,mf.HighLeverMaintenanceStatus,mf.HighLeverMainCheckTime,mf.HighLeverMaintenancePhoto," +
                "mf.HighLeverMaintenanceTime\n" +
                "FROM tb_highLever_fault mf " +
                "WHERE mf.HighLeverMaintenanceStatus=" + maintenanceStatus + "";
        if (actorIds.indexOf("7") >= 0) {
            sql += " AND mf.HighLeverMainOrgId=" + orgId + "";
        } else if (actorIds.indexOf("5") >= 0) {
            sql += " AND mf.HighLeverOrgId=" + orgId + "";
        }
        sql += " ORDER BY mf.HighLeverMainCheckTime DESC ";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(HighLeverFaultDto.class));
        return query.setFirstResult((count - 1) * size).setMaxResults(size).getResultList();
    }

    public List<HighLeverFaultDto> getHighLeverFaultList4App(Integer orgId, String actorIds, Integer faultStatus, Integer count, Integer size) throws Exception {
        String sql = "SELECT cf.HighLeverFaultPhoto,cf.HighLeverId,cf.HighLeverFaultType,cf.HighLeverFaultReason,cf.HighLeverFaultId,cf.HighLeverFaultLevel,cf.HighLeverFaultTime,cf.HighLeverCheck,cf.HighLeverMainCheck\n" +
                "FROM tb_highLever c,tb_highLever_fault cf\n" +
                "WHERE c.HighLeverId=cf.HighLeverId\n";
        //-1:未通过；0：待审核；1：已通过
        if (faultStatus == -1) {
            sql += " AND (cf.HighLeverCheck=-1 OR cf.HighLeverMainCheck=-1) ";
        } else if (faultStatus == 0) {
            sql += " AND ((cf.HighLeverCheck=1 AND cf.HighLeverMainCheck=0) OR cf.HighLeverCheck = 0) ";
        } else if (faultStatus == 1) {
            sql += " AND cf.HighLeverMainCheck = 1 ";
        }
        if (actorIds.indexOf("1") >= 0) {
            sql += "";
        } else {
            sql += " AND cf.HighLeverOrgId = " + orgId + " ";
        }
        sql += "ORDER BY cf.HighLeverFaultTime DESC";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(HighLeverFaultDto.class));
        return query.setFirstResult((count - 1) * size).setMaxResults(size).getResultList();
    }
}
